﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using DevComponents.DotNetBar.Controls;
using System.Globalization;



namespace Project_ScansiaPacific
{
    class Data 
    {
        public SqlConnection Connect()//Ket Noi
        {
            
            /*string Ketnoi = @"
            server=.\sqlexpress;
            integrated security=true;
            database=DataBase_ScansiaPacific2013";
           //*database=DataBase_ScansiaTYC";
              
          //string Ketnoi = @"Data Source=192.168.1.9;Initial Catalog=DataBase_ScansiaPacific;User ID=sa;Password=vevoianh;";
            //Version 2012
           //string Ketnoi = @"Data Source=server_sql;Initial Catalog=DataBase_ScansiaPacific;User ID=sa;Password=vevoianh;";
            //Version 2013*/
            /*string Ketnoi = @"
            server=.\sqlexpress;
            integrated security=true;
            database=DataBase_ScansiaPacific2013";
             //*/
            //string Ketnoi = @"Data Source=192.168.1.244;Initial Catalog=DataBase_ScansiaPacific2013;User ID=sa;Password=vevoianh;";
           //string Ketnoi = @"Data Source=192.168.1.5;Initial Catalog=DataBase_ScansiaPacific2013;User ID=sa;Password=vevoianh;";
          string Ketnoi = @"Data Source=server_sql;Initial Catalog=DataBase_ScansiaPacific2013;User ID=sa;Password=vevoianh;";
           SqlConnection conn = new SqlConnection(Ketnoi);
           return conn;
        }
        /*user ID=sa;
           Password=vevoianh;*/
        //public static string remember;
        public void reset_txt(GroupPanel gr)//Xoa ky tu trong txtbox
        {
            foreach (Control ct in gr.Controls)
                if (ct is TextBox)
                {
                    ((TextBox)ct).Text = "";
                }
        }
        public void reset_group_txt(GroupBox gr)//Xoa ky tu trong txtbox
        {
            foreach (Control ct in gr.Controls)
                if (ct is TextBox)
                {
                    ((TextBox)ct).Text = "";
                }
        }
        public bool check_input(string sql_select, string s, int ColumnCheck)//Kiem tra 1 gia tri input voi field
        {
            SqlConnection Conn = Connect();
            Conn.Open();
            SqlCommand cmd = new SqlCommand(sql_select, Conn);
            SqlDataReader rd = cmd.ExecuteReader();
            while (rd.Read())
            {
                if (s == rd[ColumnCheck].ToString())
                    return true;
            }
            Conn.Close();
            return false;
        }
        public void Change_data(string sql_select)//them,xoa,sua,delete du lieu
        {
            try
            {
                SqlConnection Conn = Connect();
                Conn.Open();
                SqlCommand cmd = new SqlCommand(sql_select, Conn);
                cmd.ExecuteNonQuery();
                Conn.Close();
                cmd.Dispose();
            }
            catch (Exception ex)//bắt lỗi insert
            {
                string s = "insert duplicate key";
                string FK = "FOREIGN KEY constraint";
                string s1 = ex.Message.ToString();
                if (s1.Contains(s))
                { MessageBox.Show("Insert không thành công \n" + "Mã đã tồn tại "); }
                else if (s1.Contains(FK))
                { MessageBox.Show("Nhập sai thông tin trong Combobox"); }
                else
                    MessageBox.Show(ex.Message);
            }

        }
        public DataSet dts(string sql)//Khoi tao dataset
        {
            SqlConnection Conn = Connect();
            Conn.Open();
            SqlDataAdapter da = new SqlDataAdapter(sql, Conn);
            DataSet ds1 = new DataSet();
            da.Fill(ds1);
            Conn.Close();
            da.Dispose();
            return ds1;
        }
        public DataSet dts_sql(SqlCommand cmd)
        {
            SqlConnection Conn = Connect();
            DataSet ds = new DataSet();
            Conn.Open();
            cmd.Connection = Conn;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            da.Dispose();
            Conn.Close();
            return ds;
           
        }
        public string filter(string sql)//xu ly chuoi tim kiem
        {
            return sql.Substring(0, sql.Length - 5);
        }
        public void reset_cb(GroupPanel gr)
        {
            foreach (Control ct in gr.Controls)
                if (ct is ComboBox)
                {
                    ((ComboBox)ct).Text = "";
                }

        }
        public int count_table(string sql)
        {
            int i = 0;
            SqlConnection Conn = Connect();
            Conn.Open();
            SqlCommand cmd = new SqlCommand(sql, Conn);
            SqlDataReader rd = cmd.ExecuteReader();
            while (rd.Read())
            {
                i++;
            }
            Conn.Close();
            cmd.Dispose();
            return i;
        }
        public void datimes(Label lb_date)
        {
            int day = DateTime.Now.Day;
            int thang = DateTime.Now.Month;
            int nam = DateTime.Now.Year;
            lb_date.Text = "Ngày " + day.ToString() + " Tháng " + thang.ToString() + " Năm " + nam.ToString();
        }
        public void backup()
        {
            try
            {
                SqlConnection Conn = Connect();
                Conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = @"BACKUP DATABASE DataBase_ScansiaPacific
                                TO DISK = 'D:\Scansia Pacific\project_ScansiaPacific\Backup_DataBase.Bak'
                                with format,
                                NAME = 'Full Backup of DataBase_ScansiaPacific';";
                cmd.Connection = Conn;
                cmd.ExecuteNonQuery();
                Conn.Close();
                cmd.Dispose();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Lỗi xảy ra khi backup database" + ex.Message);
            }

        }
        public string formatDate(string s)
        {
            DateTime date = DateTime.ParseExact(s, new string[] { "dd/MM/yyyy" }, CultureInfo.CurrentCulture, DateTimeStyles.AllowWhiteSpaces);
            string date2 = string.Format("{0: MM/dd/yyyy}", date);
            return date2;
            //MessageBox.Show(date2);
        }
        public void formatcolordatagrid(DataGridViewX grid)
        {
            
            int k = 0;
            for (int i = 0; i < grid.Rows.Count - 1; i++)
            {
                k = i + 1;
                grid.Rows[i].HeaderCell.Value = k.ToString();
                if (k % 2 == 0)
                {
                    grid.Rows[i].DefaultCellStyle.BackColor = Color.AliceBlue;
                }
                else
                {
                    grid.Rows[i].DefaultCellStyle.BackColor = Color.WhiteSmoke;
                }
            }
        }
        public void ExportExcel(DataTable dt, string path,int rowStart,int columnStart)
        {

            Microsoft.Office.Interop.Excel.Application ExcelObj = new Microsoft.Office.Interop.Excel.Application();
            ExcelObj.Visible = true;

            Microsoft.Office.Interop.Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, false, Microsoft.Office.Interop.Excel.XlCorruptLoad.xlNormalLoad);
            Microsoft.Office.Interop.Excel.Sheets sheet = theWorkbook.Worksheets;
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheet.get_Item(1);
            //worksheet.Activate();
            object[,] arr = new object[dt.Rows.Count, dt.Columns.Count];
            //Chuyển dữ liệu từ DataTable vào mảng đối tượng
            for (int r = 0; r < dt.Rows.Count; r++)
            {
                DataRow dr = dt.Rows[r];
                for (int c = 0; c < dt.Columns.Count; c++)
                {
                    //DataRow dr = dt.Rows[r];
                    arr[r, c] = dr[c];
                }
            }

            //Thiết lập vùng điền dữ liệu

            int rowEnd = rowStart + dt.Rows.Count - 1;
            //int rowEnd = rowStart + dt.Columns.Count - 1;

            int columnEnd = columnStart + dt.Columns.Count - 1;
            //int columnEnd = dt.Rows.Count - 1 + columnStart;

            // Ô bắt đầu điền dữ liệu
            Microsoft.Office.Interop.Excel.Range c1 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[rowStart, columnStart];
            // Ô kết thúc điền dữ liệu
            Microsoft.Office.Interop.Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[rowEnd, columnEnd];
            // Lấy về vùng điền dữ liệu
            Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(c1, c2);

            //Điền dữ liệu vào vùng đã thiết lập
            range.Value2 = arr;
            //range.Columns.AutoFit();
            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
            //theWorkbook=theWorkbook.Open("C:\\test.xlsx");
            //worksheet = ("sheet1");
            //range.Activate();
            object misValue = System.Reflection.Missing.Value;
            theWorkbook.Close(true, misValue, misValue);
            ExcelObj.Quit();

        }
    }
    class FormProcess//Lop xu ly cac su kien thuoc form
    {
        public void cb1(ComboBox cb, string sql, int column1, int column2)
        {
            Data dta = new Data();
            SqlConnection Conn = dta.Connect();
            try
            {
                Conn.Open();
                SqlCommand cmd = new SqlCommand(sql, Conn);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    cb.Items.Add(rd[column1].ToString() + " - " + rd[column2].ToString());
                }
                Conn.Close();
                cmd.Dispose();
                rd.Close();
            }
            catch (Exception ex)
            { MessageBox.Show("Lỗi load Combobox " + ex.Message); }

        }
        //Su dung cho textbox la kieu so
        public bool isfloat(string s)
        {
            float f = 0;
            if (float.TryParse(s, out f))
                return true;
            else
                return false;
        }
        public bool isInt(string s)
        {
            int i = 0;
            if (int.TryParse(s, out i))
                return true;
            else
                return false;
        }
        public void format_number(TextBox txt)
        {
            txt.Text = string.Format("{0:###,###,###,###.##}", double.Parse(txt.Text));//ép kiểu float,nếu xảy ra ngoại lệ thì bẫy lỗi ở hàm checktext_number
            txt.SelectionStart = txt.Text.Length;
        }
        public void format_numberlb(Label lb)
        {
            lb.Text = string.Format("{0:###,###,###,###}", double.Parse(lb.Text));//ép kiểu float,nếu xảy ra ngoại lệ thì bẫy lỗi ở hàm checktext_number
        }
        public string format_floatstr(double f)
        {
            string s;
            s = string.Format("{0:###,###,###,###}", f);
            return s;
        }
        public void checktext_number(TextBox txt)
        {
            try
            {
                if (txt.Text.Length > 0)
                {
                    FormProcess Frp = new FormProcess();
                    Frp.format_number(txt);
                }
            }
            catch (Exception ex)//Bẫy lỗi khi nhập chữ vào text box
            {
                string s = "Input string";
                if (ex.Message.ToString().Contains(s))
                {
                    MessageBox.Show("Giá trị nhập phải là số");
                    txt.Text = "";
                }
                else
                    MessageBox.Show(ex.Message);
            }
        }
        //
    }
    class Process : Data
    {
        public int index_string(string Parent, string child)
        {
            int j = 0, i, check = -1;
            for (i = 0; i < Parent.Length; i++)
            {
                if (child[j] == Parent[i])
                {
                    if (j == child.Length - 1)
                    {
                        check = i;
                        break;
                    }
                    else
                        j++;
                }
                else
                    j = 0;
            }
            return check;
        }//Tim chuoi con trong chuoi cha va tra ve vi tri cuoi cung cua chuoi con trong chuoi cha
        public string substring_left(string parent, string child)
        {
            int index = index_string(parent, child);
            string s = "";
            if (index == child.Length - 1)
                s = "";
            if (index == parent.Length - 1)
                s = parent.Substring(0, parent.Length - child.Length);
            if (index > child.Length - 1 && index < parent.Length - 1)
                s = parent.Substring(0, index - child.Length + 1);
            return s;
        }
        public string substring_right(string parent, string child)
        {
            int index = index_string(parent, child);
            string s = "";
            if (index == child.Length - 1)
                s = parent.Substring(index + 1, parent.Length - child.Length);
            if (index == parent.Length - 1)
                s = "";
            if (index > child.Length - 1 && index < parent.Length - 1)
                s = parent.Substring(index + 1, parent.Length - index - 1);
            return s;
        }

    }
}
